"""
@version: v1.0
@author: wangwenjie
@Email: wwjessie1997@outlook.com
@software: PyCharm
@project: Tool-Code
@file: Get Data.py
@time: 2025/1/26 13:42
@desc: 画图表
"""

import os
import rqdatac
import pymysql
from sqlalchemy import create_engine
pymysql.install_as_MySQLdb()
import pandas as pd

# 从本地文件获取数据
def get_data_from_LocalFile():
    # 获取本地地址方式1
    file_path1 = r"F:\Seafile\02. 研究工作资料汇总\14. 国债期货\基差收敛验证.xlsx"
    df = pd.read_excel(file_path1, sheet_name = sheetname) # 从excel提取
    file_path2 = r'data/data_all.csv'
    df = pd.read_csv(file_path2, sep=',', header='infer') # 从csv提取
    # 获取本地地址方式2
    path = os.getcwd()
    df = pd.read_excel(os.path.join(path, 'params/xxx.xlsx'), sheet_name = sheetname)
    df = pd.read_pickle(os.path.join('data', 'xxx.pkl'))  # data为path下面存储数据的文件夹
    return df

# 从MySql获取数据
def get_mysql_data():
    # 提取方式1
    conf = {'username': 'hs_wangwenjie',
            'password': 'hs_wangwenjie#A0',
            'host': '192.168.201.185',
            'port': 3306,
            'db': 'wangwj'}
    url = 'mysql+pymysql://%s:%s@%s:%s/%s' % (conf['username'], conf['password'], conf['host'], conf['port'], conf['db'])
    engine = create_engine(url)
    query = ("""select DISTINCT t_date from conv_factor_new where factor_name = '%s' ORDER BY t_date""") % (fn)
    df = pd.read_sql_query(query, engine)

    # 提取方式2
    conn = pymysql.connect(host='192.168.201.187',
                           port=3306, user='hs_wangwenjie', passwd='hs_wangwenjie#A0',
                           database='touyan', charset='utf8')
    sql = """select c_fund_id, c_fund_name, t_date_std, n_swanav from s_fund_nv_w where c_fund_id in {} and t_date_std <= '{}' and n_flag=1""".format(tuple(fund_ids), update_date)
    df = pd.read_sql(sql, conn).sort_values(by=['c_fund_id', 't_date_std'])
    return df

# 从RiceQuant获取数据
def get_rq_data():
    # 数据字典：https://www.ricequant.com/doc/rqdata/python/
    rqdatac.init('license','AcBHy5_JJ6wjZdu7Q-ey7dX-J3BmyEC_KblY2Q_hBeOuoBaeBbgXTNSe6XZvqKVESbyUf7vMpLLGuO_aqyb3w9fWGI7q4wdClE6cMp_Z3N4PqqTHJ0nr3CIuXtk-5XzSD1p7NTdNcrAfZlRVpMMtY_PDC9FYuXNmC_EnuQg4H-A=fGk9EhHcK3xN189iXYSWLyiMdGUeXXlVZqr2MxhBypSHxQYnIIyxyM8BR8oNnVUdWhKx-ZrFRIjSONd7uYpOvpcBab92P60iAR_JopX61emtrvsY1xG_uCfYhDPBdDSJKaniJhTPuoBIU4JZun8-8fMIxzx7lnwBm2kAUOA_Mpg=')
    df = rqdatac.get_price(code, start_date=start, end_date=end, frequency='1m', fields=None,
                             adjust_type='none', skip_suspended=False, market='cn', expect_df=True, time_slice=None)
    return df

# 从Wind获取数据
def get_winddata1(start, end):
    # wind终端快捷键CG，实例为提取指数收盘价数据
    data = w.wsd("889033.WI", "close", start, end, "Period=D;PriceAdj=B")
    df = pd.DataFrame(np.array(data.Data).T, index=data.Times, columns=['benchmark'])
    return df

# 从Wind底库获取数据
def get_winddata2():
    # 参考Wind数据字典.zip
    wind_engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # sql第一种写法
    sql_str1 = 'SELECT TRADE_DAYS, S_INFO_EXCHMARKET' + ' FROM ' + 'AShareCalendar' + ' WHERE ' + 'S_INFO_EXCHMARKET = "SSE"'
    # sql第二种写法
    sql_str2 = ("""select S_INFO_WINDCODE,TRADE_DT,S_VAL_MV,S_VAL_PE_TTMfrom AShareEODDerivativeIndicator
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code, str_date)
    df1 = pd.read_sql(sql_str1, wind_engine)
    df2 = pd.read_sql(sql_str2, wind_engine)
    return df1, df2

# 从HS基础数据库获取数据
def get_convbond_market(start_date,end_date,tableName,fields=None):
    # 数据字典：http://172.29.88.22:8052/4.%E5%9F%BA%E7%A1%80%E6%95%B0%E6%8D%AE/%E5%80%BA%E5%88%B8%E6%95%B0%E6%8D%AE/%E5%8F%AF%E8%BD%AC%E5%80%BA%E4%BC%B0%E5%80%BC%E8%A1%A8.html
    path = 'http://dataway.hhhstz.com/hsic_base_fmt/cube?'
    tableName = tableName
    query_str = path + 'tableName=' + tableName + '&begDate=' + start_date + '&endDate=' + end_date
    if fields != None: query_str += '&fields=c_code,t_date,' + ','.join(fields)
    data = pd.read_csv(query_str)
    return data

# 导出数据代码
def data_output(Port):
    # 导出数据到excel
    path = os.getcwd()
    new_dir = os.path.join(path, 'output/{}'.format(update_date))
    if not os.path.exists(new_dir):
        os.makedirs(new_dir)
    wb = pd.ExcelWriter(os.path.join(new_dir, "{}.xlsx".format(name)))
    Port.to_excel(wb, '%s' % (name), index=False)
    wb.save()
    # 导出数据到csv
    Port.to_csv(r"param/xxx.csv", index=False)
    # 导出数据到pickle
    Port.to_pickle(os.path.join('data', 'xxx.pkl'))  # data为path下面存储数据的文件夹
    # 导出数据到MySql
    conf = {'username': 'hs_wangwenjie',
            'password': 'hs_wangwenjie#A0',
            'host': '192.168.201.185',
            'port': 3306,
            'db': 'market_indicator_monitor'}
    uri = 'mysql+pymysql://%s:%s@%s:%s/%s' % (conf['username'], conf['password'],conf['host'], conf['port'], conf['db'])
    engine = create_engine(uri)
    first_time_to_sql = False
    if first_time_to_sql:
        port.to_sql('indi_fixincome_market_value', engine, index=False,)
    else:
        port.to_sql('indi_fixincome_market_value', engine, index=False, if_exists='append')
    return

